#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "Q1mi"
# Date: 2018/10/29

# 根据气象规则，获取温馨提示----规则和条件的筛选

import pymssql
import pandas as pd
from datetime import timedelta
import time
import matplotlib.pyplot as plt
from dingtalkchatbot.chatbot import DingtalkChatbot
import numpy as np
import math
from weater_rule_01 import Expression

#########运行时间

def fo(fun):
    def md(*args,**kwargs):
        begin_time = time.time()
        reg= fun(*args,**kwargs)  #调用下面的man（）函数
        print('======reg')
        print(time.time() - begin_time)
        return reg
    return md

def linefit(x, y):
    N = float(len(x))
    sx, sy, sxx, syy, sxy = 0, 0, 0, 0, 0
    for i in range(0, int(N)):
        sx += x[i]
        sy += y[i]
        sxx += x[i] * x[i]
        syy += y[i] * y[i]
        sxy += x[i] * y[i]
    a = (sy * sx / N - sxy) / (sx * sx / N - sxx)
    b = (sy - a * sx) / N
    if math.sqrt((sxx - sx * sx / N) * (syy - sy * sy / N)) == 0:
        r= 0
    else:
        r = abs(sy * sx / N - sxy) / math.sqrt((sxx - sx * sx / N) * (syy - sy * sy / N))
    return a, b, r
# ----------------------------读取sql数据---------------------------------------------------
class Sql(object):
    #sql_pre：sql语句，初始化
    def __init__(self,sql_tabel="istrong_data_collection"):
       # self.sql_pre = sql_pre
        self.server = "119.29.146.108"
        self.user = "sa"
        self.password = "S3686-e342qq-3686e342"
        self.table = sql_tabel
    #获取sql链接
    def Obtain_connect(self):
        connect = pymssql.connect(self.server, self.user, self.password, self.table, charset="utf8")  # 获取连接
        cursor = connect.cursor()  # 获取光标
        return cursor,connect

    #查询sql
    def Obtain_sql(self,sql_pre):
        cursor, connect = self.Obtain_connect()  # 获取光标

        cursor.execute(sql_pre)
        number_pre = cursor.fetchall()
        connect.close()
        return number_pre

    #将查询的sql转化为dataframe
    def dataframe_sql(self,sql_pre,sql_str):
        number_pre = self.Obtain_sql(sql_pre)
        columns = [x for x in sql_str.split(',')]
        weater_data_pre = pd.DataFrame(number_pre, columns=columns)
        return weater_data_pre

    #插入sql
    def Insert_sql(self,sql_pre):
        cursor, connect = self.Obtain_connect()  # 获取光标

        cursor.execute(sql_pre)
        connect.commit()
        connect.close()


# ---------------获取气象数据----------------
def weater(day):
    '''
    :param day: 未来day天
    :return: 获取未来day天和前一天的最大温度，最低温度，气温，降水
    '''
    ####----------LJ_FutureWeater--------------未来day天天气
    sql_str_weater = 'STNM,wd_max,wd_min,weater,js_max,c1,c2,YBTM,PSTM'
    sql_pre_weater = "SELECT   {} from {} WHERE PSTM = (select max(PSTM) from {}) ".format(
        sql_str_weater, 'LJ_FutureWeater', 'LJ_FutureWeater')

    sql_data = Sql()
    weater = sql_data.dataframe_sql(sql_pre_weater, sql_str_weater)
    now_time = weater['YBTM'].min()
    now_time_n = (now_time + timedelta(days=day - 1)).strftime("%Y-%m-%d")
    weater = weater[weater['YBTM'] <= now_time_n]

    ####--------前1天的数据-------------------
    sql_str_weater_pre = 'STNM,wd_max,wd_min,weater,js_max,c1,c2,YBTM,PSTM'

    now_time_1 = (now_time + timedelta(days=-1)).strftime("%Y-%m-%d")

    sql_pre_weater_pre = "SELECT   {} from {} WHERE PSTM = (select max(PSTM) from {}  WHERE  YBTM= '{}' ) and YBTM= '{}' ".format(
        sql_str_weater_pre, 'LJ_FutureWeater', 'LJ_FutureWeater', now_time_1, now_time_1)

    weater_pre = sql_data.dataframe_sql(sql_pre_weater_pre, sql_str_weater)

    ##合并-----------------
    weater_data = pd.concat([weater_pre, weater], ignore_index=True)

    return weater_data

def draw(original_data,code_name_out):
    a_max, b1, r1 = linefit(range(len(original_data)), original_data['wd_max'].astype(int).tolist())
    a_min, b2, r2 = linefit(range(len(original_data)), original_data['wd_min'].astype(int).tolist())
    test_y_max = [a_max * x + b1 for x in range(len(original_data))]
    test_y_min = [a_min * x + b2 for x in range(len(original_data))]
    plt.rcParams['font.sans-serif'] = ['SimHei']

    #print('白天平均温度{},晚上平均温度{}'.format(original_data['wd_max'].astype(int).mean(),original_data['wd_min'].astype(int).mean()))
    x =original_data['YBTM'].tolist()# range(len(self.original_data))

    plt.plot(x, original_data['wd_max'].astype(int).tolist(), 'ro-', label="最大值")  # 蓝色--较好
    plt.plot(x, test_y_max, 'r',
             label="max趋势")  # 红色
    plt.plot(x, original_data['wd_min'].astype(int).tolist(), 'bo-', label="最小值")  # 红色
    plt.plot(x, test_y_min, 'b',
             label="min趋势")  # 红色
    plt.legend(loc="upper right")  # 显示图中的标签
    plt.xlabel("{}".format(code_name_out))
    plt.ylabel('温度')
    plt.xticks(rotation=45)
    plt.title('白天平均温度{},晚上平均温度{},\n最高温斜率:{},最低温斜率:{}'.format(round(original_data['wd_max'].astype(int).mean(),3),round(original_data['wd_min'].astype(int).mean()),round(a_max,3),round(a_min,3)))
    plt.show()


@fo
def main(day,stnm,rqsj):

    ############1、获取天气数据######################
    weater_data = weater(day)[weater(day)['STNM'] == str(stnm)]  # 获取福州的天气

    ############2、气象规则########################

    df2 = weater_data.iloc[:-1, :].reset_index().iloc[:, 1:]#前一天的数据
    df1 = weater_data.iloc[1:, :].reset_index().iloc[:, 1:]

    df2.columns = df2.columns.map(lambda x: x + '_1')
    original_data_wt = pd.concat([df1, df2], axis=1)
    original_data_wt['time'] = [x.replace('-', '月').replace(':', '日') for x in original_data_wt['YBTM'].apply(
        lambda x: x.strftime("%m{m}%d{d}".format(m='-', d=':'))).tolist()]

    rule_data = Expression(day,original_data_wt)
    rule_data.simple_day()
    expression_day =rule_data.text

    draw(original_data_wt,stnm)
    return expression_day


def test(stnm,rqsj,sql_code=False,ding_code=True):
    days = [7,15,40]
    fz_dic = {}
    text_sql = []
    for day in days:
        data = main(day,stnm,rqsj)
        print(data)
       # 钉钉发起post请求
        time.sleep(5)
        if ding_code:
            # WebHook地址
            webhook = 'https://oapi.dingtalk.com/robot/send?access_token=c023ebfff1eef6e24393f4f1d82c11a7a589edbcb10b56f4b7bbcf463091a67d'
            # 初始化机器人小丁
            xiaoding = DingtalkChatbot(webhook)
            # Text消息@所有人
            xiaoding.send_text(msg=data, is_at_all=None)

        ##数据整合
        save_text_pr = []
        save_text_pr.append(stnm)
        save_text_pr.append(day)
        save_text_pr.append(data.strip('\n').replace('\n',','))
        save_text_pr.append(rqsj)
        text_sql.append(tuple(save_text_pr))


        ###########福州的
        fz_dic[stnm] = data

    # 保存到sql格式
    if sql_code:

        for x in text_sql:
            insert_sql_str = 'insert into LJ_Tips(code,time,tips,rqsj) values {}'.format(x)
            print(insert_sql_str)
            sql_data = Sql()
            sql_data.Insert_sql(insert_sql_str)
            print('===========完成一次_{}插入'.format(x))



if __name__ == "__main__":

    ##test########
    address_dic = {'福州市':101230101}#,'温州':101210701
        #,'厦门市':101230201,'宁德市':101230301,'莆田市':101230401,'泉州市':101230501,'漳州市':101230601,'龙岩市':101230701,'三明市':101230801,'南平市':101230901}

    stnm = '101230101'
    data = [x.update({'c': x.pop("a")}) for x in dict]
    #########运行一次###############
    rqsj = time.strftime('%Y-%m-%d %H:00:00', time.localtime())
    for stnm_1 in address_dic.values():
        test(stnm_1,rqsj,ding_code=False,sql_code=False)


###########正式#########################
    # # days = [7,15,40]
    while True:
        time.sleep(3600)
        now_M = time.strftime('%H', time.localtime())
        print('============time')
        print(now_M)
        if now_M == '09' or now_M == '20':
            rqsj = time.strftime('%Y-%m-%d %H:00:00', time.localtime())
            for stnm_1 in address_dic.values():
                test(stnm_1,rqsj)
            print('=============完成一次更新')

